import json
import sqlite3
from aip import AipOcr

ORC_APP_ID = "116015521"
ORC_API_KEY = "PZj84fTDt3CqmBkw1BCIWktP"
ORC_SECRET_KEY = "vypcTlcGXEiVXTLWcb3QO4dqIZ7i6z8E"

class SQL:

    def __init__(self, db_name: str):
        self.conn = sqlite3.connect(db_name)
        self.cursor = self.conn.cursor()
        self.cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='books';")  

        result = self.cursor.fetchone()  
        if result is None:
            self.cursor.execute(
            """
                CREATE TABLE books
                (
                    book_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT UNIQUE NOT NULL,
                    top INTEGER NOT NULL,
                    left INTEGER NOT NULL
                );
            """)
    
    def __del__(self):
        self.conn.commit()
        self.cursor.close()
        self.conn.close()
                
    def InsertTable(self, data):
        try:
            self.cursor.executemany("insert into books (name, top, left) values(?, ?, ?)", data)
        except sqlite3.IntegrityError:  
            print("Unique key conflict. Record not inserted.")
                
    def SelectTable(self):
        self.cursor.execute(f"select * from books")
        # 获取所有查询结果  1
        rows = self.cursor.fetchall()  
        # 遍历并打印每一行结果  
        for row in rows:  
            print(row)  
        
    def SearchTable(self, bookname):
        try:
            self.cursor.execute(f"select name, top, left from books where name='{bookname}'")
        except:
            print("这本书不存在！")
        result = []
        result.append(self.cursor.fetchone())
        if len(result) == 0:
            return True
        else:
            return False


sql = SQL("Test\db\Test4.db")

def Learn(): 
    print("开始学习！")
    result = []

    ocr = AipOcr(ORC_APP_ID, ORC_API_KEY, ORC_SECRET_KEY)
    with open(f'Test/image/test4.png', 'rb') as file:  
        image = file.read()
    result = ocr.accurate(image)
    print("返回数据成功！")
    print(result)
    
    # 提取所有书名和对应的位置信息
    book_info = {item['words']: item['location'] for item in result['words_result']}
    data = []    
    result.clear()
    for book_name, book_location in book_info.items():
        if len(book_name) <= 3:
            continue
        if book_name[1] == '法' or book_name[1] == '英':
            continue
        if (book_name[0] >= 'a' and book_name[0] <= 'z') or (book_name[0] >= 'A' and book_name[0] <= 'Z'):
            continue
        
        if book_name[0] >= '0' and book_name[0] <= '9':
            continue
        
        book_left = book_location['left']
        book_top = book_location['top']
        
        data.append((book_name, book_top, book_left))
        if sql.SearchTable(book_name) == False:
            sql.InsertTable(data)
        data.clear()
    
    print("学习成功！")

Learn()
sql.SelectTable()


        
    
